﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.EntityClient;
using System.Data.SqlClient;
namespace YEBHI.Utilities
{
    public sealed class EFUtils
    {
        private EFUtils()
        {
        }
        public static DataTable ExecuteProc(string uspProcedure, params SqlParameter[] oSqlParameterArr)
        {
            try
            {
                EntityConnection oEntityConnection = new EntityConnection("name=YEBHIEntities");
                using (SqlConnection oSqlConnection = new SqlConnection(oEntityConnection.StoreConnection.ConnectionString))
                {
                    oEntityConnection.Dispose();
                    SqlCommand oSqlCommand = oSqlConnection.CreateCommand();
                    oSqlCommand.CommandType = CommandType.StoredProcedure;
                    oSqlCommand.CommandText = uspProcedure;
                    foreach (SqlParameter oSqlParameter in oSqlParameterArr)
                    {
                        oSqlCommand.Parameters.Add(oSqlParameter);
                    }
                    oSqlCommand.Connection.Open();
                    SqlDataReader oSqlDataReader = oSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable dtRecords = null;
                    dtRecords.Load(oSqlDataReader);
                    return dtRecords;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public static DataSet ExecuteProcDataset(string uspProcedure, params SqlParameter[] oSqlParameterArr)
        {
            try
            {
                EntityConnection oEntityConnection = new EntityConnection("name=YEBHIEntities");
                using (SqlConnection oSqlConnection = new SqlConnection(oEntityConnection.StoreConnection.ConnectionString))
                {
                    oEntityConnection.Dispose();
                    SqlCommand oSqlCommand = oSqlConnection.CreateCommand();
                    oSqlCommand.CommandType = CommandType.StoredProcedure;
                    oSqlCommand.CommandText = uspProcedure;
                    foreach (SqlParameter oSqlParameter in oSqlParameterArr)
                    {
                        oSqlCommand.Parameters.Add(oSqlParameter);
                    }
                    SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(oSqlCommand);
                    DataSet dsRecords = null;
                    oSqlDataAdapter.Fill(dsRecords);
                    return dsRecords;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public static DataTable ExecuteQueryTran(string strQuery)
        {
            try
            {
                EntityConnection oEntityConnection = new EntityConnection("name=YEBHIEntities");
                using (DataTable dtRecords = new DataTable())
                {
                    using (SqlConnection oSqlConnection = new SqlConnection(oEntityConnection.StoreConnection.ConnectionString))
                    {

                        oEntityConnection.Dispose();
                        oSqlConnection.Open();
                        SqlTransaction oSqlTransaction = oSqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
                        using (SqlCommand oSqlCommand = oSqlConnection.CreateCommand())
                        {
                            oSqlCommand.CommandType = CommandType.Text;
                            oSqlCommand.CommandText = strQuery;
                            oSqlCommand.Transaction = oSqlTransaction;
                            //oSqlCommand.Connection.Open();
                            try
                            {
                                using (SqlDataReader oSqlDataReader = oSqlCommand.ExecuteReader(CommandBehavior.SingleResult))
                                {
                                    dtRecords.Load(oSqlDataReader);
                                    oSqlTransaction.Commit();
                                }
                            }
                            catch (Exception ex)
                            {
                                oSqlTransaction.Rollback();
                                if (oSqlConnection.State == ConnectionState.Open)
                                {
                                    oSqlConnection.Close();
                                }
                                throw ex;
                            }
                            if (oSqlConnection.State == ConnectionState.Open)
                            {
                                oSqlConnection.Close();
                            }
                        }

                    }
                    return dtRecords;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}
